package com.sansi.pencilbook.mapper;

import com.sansi.pencilbook.entity.StoreBook;
import com.sansi.pencilbook.entity.StoreBookDTO;
import org.apache.ibatis.annotations.*;

import java.sql.SQLException;
import java.util.List;

/**
 * 功能说明: 书店的书籍增删改查
 *
 * @author lsy13
 */
@Mapper
public interface StoreBookMapper {

    @Select("select book_store.store_id, book_store.store_name, store_book.book_isbn, book.book_name, number, img, price, discount, store_book.discount_price, store_book.remark, store_book.time, book.book_introduction " +
            "from store_book " +
            "left join book_store on store_book.store_id = book_store.store_id " +
            "left join book on book.book_isbn=store_book.book_isbn")
    public List<StoreBookDTO> findAll() throws SQLException;

    @Select("select book_store.store_id, book_store.store_name, store_book.book_isbn, book.book_name, number, img, price, discount, store_book.discount_price, store_book.remark, store_book.time, book.book_introduction " +
            "from store_book " +
            "left join book_store on store_book.store_id = book_store.store_id " +
            "left join book on book.book_isbn=store_book.book_isbn " +
            "where book_store.store_id=#{storeId}")
    public List<StoreBookDTO> findByStore(int storeId) throws SQLException;

    @Select("select book_store.store_id, book_store.store_name, store_book.book_isbn, book.book_name, number, img, price, discount, store_book.discount_price, store_book.remark, store_book.time, book.book_introduction " +
            "from store_book " +
            "left join book_store on store_book.store_id = book_store.store_id " +
            "left join book on book.book_isbn=store_book.book_isbn " +
            "where store_book.book_isbn=#{isbn}")
    public List<StoreBookDTO> findByBookIsbn(String isbn) throws SQLException;

    @Select("select store_book.store_id, book_isbn, number, img, price, discount, discount_price, remark, time, introduction " +
        "from store_book " +
        "where store_book.book_isbn=#{isbn} and store_book.store_id=#{storeId}")
    public StoreBook findByStoreAndIsbn(int storeId, String isbn) throws SQLException;

    @Select("select book_store.store_id, book_store.store_name, store_book.book_isbn, book.book_name, number, img, price, discount, store_book.discount_price, store_book.remark, store_book.time, book.book_introduction " +
        "from store_book " +
        "left join book_store on store_book.store_id = book_store.store_id " +
        "left join book on book.book_isbn=store_book.book_isbn " +
        "where store_book.book_isbn=#{isbn} and store_book.store_id=#{storeId}")
    public StoreBookDTO findStoreNameByStoreAndIsbn(int storeId, String isbn) throws SQLException;

    @Insert("insert into store_book (store_id, book_isbn, number, img, price, discount, discount_price, remark, time, introduction) " +
            "values(#{storeId}, #{bookIsbn}, #{number}, #{img}, #{price}, #{discount}, #{discountPrice}, #{remark}), #{time}, #{introduction}")
    public int saveStoreBook(StoreBook storeBook) throws SQLException;

    @Insert("<script>" +
        "insert into store_book (store_id, book_isbn, number, img, price, discount, discount_price, remark, time, introduction) " +
        "values" +
        "<foreach collection='storeBooks' item='book' index='index' separator=','>" +
        "(#{book.storeId},#{book.bookIsbn},#{book.number},#{book.img},#{book.price}," +
        "#{book.discount},#{book.discountPrice},#{book.remark},#{book.time},#{book.introduction})" +
        "</foreach>" +
        "</script>")
    public int saveStoreBooks(List<StoreBook> storeBooks) throws SQLException;

    @Update("update store_book set  number=#{number}, " +
            "img=#{img}, price=#{price}, discount=#{discount}, discount_price=#{discountPrice} , remark=#{remark} , time=#{time}, #{introduction}" +
            "where store_id=#{storeId} and book_isbn=#{bookIsbn}")
    public int updateStoreBook(StoreBook storeBook) throws SQLException;

    @Update("<script>" +
        "<foreach collection='storeBooks' item='storeBook' index='index' separator=','>" +
        "update store_book set number=#{storeBook.number}," +
        "img=#{storeBook.img}, price=#{storeBook.price}, discount=#{storeBook.discount}, discount_price=#{storeBook.discountPrice} , " +
        "remark=#{storeBook.remark}, time=#{storeBook.time}, introduction=#{storeBook.introduction}" +
        "where store_id=#{storeBook.storeId} and book_isbn=#{storeBook.bookIsbn}" +
        "</foreach>" +
        "</script>")
    public int updateStoreBooks(List<StoreBook> storeBooks) throws SQLException;

    @Delete("delete from store_book where store_id=#{storeId} and book_isbn=#{bookIsbn}")
    public int deleteStoreBook(StoreBook storeBook) throws SQLException;
}
